﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using ReflectionStudio.Core.Events;
using INTEX.DataBase;
using INTEX.DataBase.Tools;
using System.Text;
namespace WPFExcelReport
{

    public class MVTVU17XData : BaseFunction 
    {
        public bool checkVK07 = true;
        SETINTEXDB INTEXDB = new SETINTEXDB();
        INTEXDBC INTEXDBC;
        public MVTVU17XData()
            : base()
        {

        }
        public MVTVU17XData(OracleConnection _myConnection, IntexReptServBLClient _proxy, string userID = "        ")
            : base(_myConnection, _proxy, userID)
        {
            INTEXDBC = new INTEXDBC(OracleDB, proxy);
        }
        public MVTVU17XData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {

        }
        public DataSet GetFSSTUELI(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "ICC_FS05";
            string ARTICLE = dwABPOS["ARTICLE"].ToString().Trim();
            string COLL = dwABPOS["COLL"].ToString().Trim();
            string REASON = dwABPOS["REASON"].ToString().Trim();
            string PRDGRP = dwABPOS["PRDGRP"].ToString().Trim();
            string ARTGRP = dwABPOS["ARTGRP"].ToString().Trim();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = " select distinct a.fssart,d.fsabez, "
                    + " agg(DISTINCT trim(a.fssfarbe)||trim(e.fsffbez)||'-'||trim(a.fssmfarbe)||trim(c.wafbez)) "
                    + " over(partition by a.fssart,a.fssmart,a.fssverbr,a.fssverl) as color, "
                    + " agg(DISTINCT trim(a.fssgroe)||'-'||trim(a.fssmgroe)) "
                    + " over(partition by a.fssart,a.fssmart,a.fssverbr,a.fssverl) as size1, "
                    + " decode(fssmatkz,'F','FAB','ACC') as mtype, "
                    + " a.fssmart,b.wabez,b.walohna,f.lsname1, "
                    + " a.fssverbr*0.001 as useage,a.fssvbme,a.fssverl*0.01 as consumption  "
                    + " from fsstueli a,wastamm b, "
                    + " (SELECT c.wafmandant,c.wafart,c.wafzusatz,c.wafbez FROM wafarben c  "
                    + " WHERE c.wafzuart = 'F' AND C.WAFSTATUS ='A') C, "
                    + " fsstamm d,fsfarben e,liefdat f "
                    + " where a.fssmatkz = 'F' and a.fssmandant = d.fsmandant "
                    + " and a.fssart = d.fsart and a.fssmandant = b.wamandant "
                    + " and a.fsskundnr = b.wakundnr and a.fsswerk = b.wawerk  "
                    + " and a.fssabt = b.waabt and a.fssmart = b.waart  "
                    + " and a.fssmandant = c.wafmandant(+) "
                    + " and a.fssmart = c.wafart(+) and a.fssmfarbe = c.wafzusatz(+) "
                    + " and a.fssmandant = e.fsfmandant(+) and a.fssart = e.fsfart(+) "
                    + " and a.fssfarbe = e.fsffarbe(+)  "
                    + " and b.wamandant = f.lsmandant(+) and b.waliefnr = f.lsliefnr(+) "
                    + " and d.fsstakz = 'A' AND b.Wafstakz = 'A' "
                    + " and a.fssart like '" + ARTICLE + "%'  "
                    + " AND (TRIM(d.fskoll) = '" + COLL + "' OR '" + COLL + "'  = 'XXXXXX') "
                    + " AND (TRIM(d.fsartprgr) = '" + REASON + "' OR '" + REASON + "'  = 'XXXXXX') "
                    + " AND (TRIM(d.fsartgrp) = '" + PRDGRP + "' OR '" + PRDGRP + "'  = 'XXXXXX') "
                    + " AND (TRIM(d.fsmatart) = '" + ARTGRP + "' OR '" + ARTGRP + "'  = 'XXXXXX') "
                    + " UNION ALL "
                    + " select distinct a.fssart,d.fsabez, "
                    + " agg(DISTINCT trim(a.fssfarbe)||trim(e.fsffbez)||'-'||trim(a.fssmfarbe)||trim(c.fsffbez)||'-'||T.REMARK) "
                    + " over(partition by a.fssart,a.fssmart,a.fssverbr,a.fssverl) as color, "
                    + " agg(DISTINCT trim(a.fssgroe)||'-'||trim(a.fssmgroe)) "
                    + " over(partition by a.fssart,a.fssmart,a.fssverbr,a.fssverl) as size1, "
                    + " decode(fssmatkz,'F','FAB','ACC') as mtype, "
                    + " a.fssmart,b.fsabez,'','',a.fssverbr*0.001 as useage,a.fssvbme, "
                    + " a.fssverl*0.01 as consumption "
                    + " from fsstueli a,fsstamm b,fsfarben c,fsstamm d,fsfarben e, "
                    + " (select SUBSTR(X.TXTISIND,1,20) AS ART,TO_NUMBER(SUBSTR(X.TXTISIND,21,4)) AS ITEM, "
                    + " SUBSTR(X.TXTISIND,31,7) AS SSIZE,trim(txtktext) AS REMARK from txtind x,txtkon y  "
                    + " where x.txtimandant = y.txtkmandant and x.txtipind = y.txtkpind  "
                    + " and y.txtkpos = 10 and x.txtitxtart = 'STP') t "
                    + " where a.fssmatkz = 'T' and a.fssmandant = d.fsmandant "
                    + " and a.fssart = d.fsart and a.fssmandant = b.fsmandant "
                    + " and a.fssmart = b.fsart and a.fssmandant = c.fsfmandant(+) "
                    + " and a.fssmart = c.fsfart(+)  and a.fssmfarbe = c.fsffarbe(+) "
                    + " and a.fssmandant = e.fsfmandant(+)  "
                    + " and a.fssart = e.fsfart(+) and a.fssfarbe = e.fsffarbe(+) "
                    + " AND A.FSSART = T.ART(+) AND A.FSSPOS = T.ITEM(+) "
                    + " and d.fsstakz = 'A' AND b.fsstakz = 'A'  "
                    + " and a.fssart like '" + ARTICLE + "%'  "
                    + " AND (TRIM(d.fskoll) = '" + COLL + "' OR '" + COLL + "'  = 'XXXXXX') "
                    + " AND (TRIM(d.fsartprgr) = '" + REASON + "' OR '" + REASON + "'  = 'XXXXXX') "
                    + " AND (TRIM(d.fsartgrp) = '" + PRDGRP + "' OR '" + PRDGRP + "'  = 'XXXXXX') "
                    + " AND (TRIM(d.fsmatart) = '" + ARTGRP + "' OR '" + ARTGRP + "'  = 'XXXXXX') "
                    + " order by 1,5 DESC "
                   ;

                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["FSABEZ"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["FSABEZ"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["COLOR"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["COLOR"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["SIZE1"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["SIZE1"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["WABEZ"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["WABEZ"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["WALOHNA"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["WALOHNA"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["LSNAME1"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["LSNAME1"] = value;
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet GetData(DataRow dwInput)
        {
            DataSet SReturn = new DataSet();
            DataSet SResult = new DataSet();

            try
            {
                DataRow dwTemp;
                SResult = GetFSSTUELI(dwInput);
                SReturn.Merge(SResult); 
            }
            catch (Exception ex)
            {
                Tracer.Error(ABPOS + ex.StackTrace, ex);
            }
            finally
            {
                EventDispatcher.Instance.RaiseStatus("查询完成", StatusEventType.StopProgress);
            }
            return SReturn;
        } 
    }
}